﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Theme_Index_rpt_schoolRank : System.Web.UI.UserControl
{
    PowerCreator.LMS.ViewQuery.ViewQuery objViewQuery = new PowerCreator.LMS.ViewQuery.ViewQuery();

    protected void Page_Load(object sender, EventArgs e)
    {
        //绑定学校排名信息 //单位学习排行
        BinderPGA();
    }
    private void BinderPGA()
    {
        string searchView = "";
        int intScoresCol = 0;
        int intScores = 0;
        int intHours = 0;
        int intWorkNum = 0;
        int intWorReply = 0;
        int intExam = 0;
        string searchStu = "select * from to_learner where School={0}";
        string search = "select top 6 * from TO_SchoolGroup where SchoolGroupParentId =0";
        DataTable dtSchool = objViewQuery.Query(search);
        dtSchool.Columns.Add("avgScore", typeof(int));
        dtSchool.Columns.Add("levelSchool");
        if (dtSchool.Rows.Count > 0)
        {
            for (int i = 0; i < dtSchool.Rows.Count; i++)
            {
                searchStu = string.Format(searchStu, dtSchool.Rows[i]["SchoolGroup_Id"].ToString());
                DataTable dtStu = objViewQuery.Query(searchStu);
                if (dtStu.Rows.Count > 0)
                {
                    for (int j = 0; j < dtStu.Rows.Count; j++)
                    {
                        //得到每个学员的总学时 
                        //string searchCourse = "select sum(SscrlogTotalTime) as studyHours,FKSscrlogUserID from TStudentStudyCouReslog group by FKSscrlogUserID having FKSscrlogUserID={0}";
                        //searchView = string.Format(searchCourse, dtStu.Rows[j]["Learner_Id"].ToString());
                        //DataTable dtCourse = objViewQuery.Query(searchView);
                        //if (dtCourse.Rows.Count > 0)
                        //{
                        //    if (string.IsNullOrEmpty(dtCourse.Rows[0]["studyHours"].ToString()))
                        //    {
                        //        intHours = 0;
                        //    }
                        //    else
                        //    {
                        //        intHours = int.Parse(dtCourse.Rows[0]["studyHours"].ToString());
                        //    }

                        //}
                        //得到作业完成总次数
                        string searchWorkNum = "select count(Creator) as WorkNum from TO_ClassTasks where Creator ={0}";
                        searchView = string.Format(searchWorkNum, dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtWorkNum = objViewQuery.Query(searchView);
                        if (dtWorkNum.Rows.Count > 0)
                        {
                            if (string.IsNullOrEmpty(dtWorkNum.Rows[0]["WorkNum"].ToString()))
                            {
                                intWorkNum = 0;
                            }
                            else
                            {
                                intWorkNum = int.Parse(dtWorkNum.Rows[0]["WorkNum"].ToString());
                            }

                        }
                        //得到作业评论总次数
                        string searchTasksReply = "select count(*) as ReplyNum ,Creator from (select  b.Creator from TO_ClassTasksReply as a ,TO_UserClassTask as b ,TO_ClassTasks as c  where a.UserTask_Id = b.UserTask_Id and b.task_Id = c.ClassTasks_Id ) as tb group by Creator  having Creator ={0} ";
                        searchView = string.Format(searchTasksReply, dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtTasksReply = objViewQuery.Query(searchView);
                        if (dtTasksReply.Rows.Count > 0)
                        {
                            if (string.IsNullOrEmpty(dtTasksReply.Rows[0]["ReplyNum"].ToString()))
                            {
                                intWorReply = 0;
                            }
                            else
                            {
                                intWorReply = int.Parse(dtTasksReply.Rows[0]["ReplyNum"].ToString());
                            }

                        }
                        //得到考试总成绩
                        string searchExam = "select sum(UPHistoryScore) as UPHistoryScore from UserPaperHistory where fKUPHistoryUserID ={0}";
                        searchView = string.Format(searchExam, dtStu.Rows[j]["Learner_Id"].ToString());
                        DataTable dtExam = objViewQuery.Query(searchView);
                        if (dtExam.Rows.Count > 0)
                        {
                            if (string.IsNullOrEmpty(dtExam.Rows[0]["UPHistoryScore"].ToString()))
                            {
                                intExam = 0;
                            }
                            else
                            {
                                intExam = int.Parse(dtExam.Rows[0]["UPHistoryScore"].ToString());
                            }

                        }
                        intScores = 0;
                        intScores = intWorkNum + intWorReply + intExam;//得到每个学员的
                    }
                    intScoresCol = intScoresCol + intScores;
                }
                if (dtStu.Rows.Count > 0)
                {
                    dtSchool.Rows[i]["avgScore"] = intScoresCol / dtStu.Rows.Count;
                }
                else
                {
                    dtSchool.Rows[i]["avgScore"] = 0;
                }
                //dtSchool.Rows[i]["avgScore"] = 0;
            }

        }
        //学校排名
        DataView dv = dtSchool.DefaultView;
        if (dtSchool.Rows.Count > 0)
        {
            dv.Sort = "avgScore desc";
            dtSchool = dv.ToTable();
            for (int j = 0; j < dtSchool.Rows.Count; j++)
            {
                dtSchool.Rows[j]["levelSchool"] = j + 1;
            }
        }

        if (dtSchool.Rows.Count > 6)
        {
            for (int k = 6; k < dtSchool.Rows.Count; k++)
            {
                dtSchool.Rows.RemoveAt(k);
                k--;
            }
        }
        rpt_schoolRank.DataSource = dtSchool;
        rpt_schoolRank.DataBind();
    }
}